function hotelNumChangeSQL(date){//酒店数量变化
    return `SELECT COUNT(*) num FROM hotel_rent WHERE gmtCreate>${date[0]} and gmtCreate<${date[1]}`;
}
function nightnumSQL(date){//总夜间晚数
    return `SELECT SUM(num) num FROM (SELECT night*roomnum num FROM (
        SELECT hds.hotelId,night,COUNT(*) roomnum FROM (
        SELECT hds.hotelId,COUNT(*) night FROM hotel_daily_settlement hds INNER JOIN
        hotel_rent hr ON hds.hotelId=hr.hotelId AND ${date[0]}<hds.from AND ${date[1]}>hds.from GROUP BY hotelId
        ) hds INNER JOIN hotel_room hr ON hds.hotelId=hr.hotelId GROUP BY hotelId
        ) hds ) hds`;
}
function occupancySQL(date){//总入住数desc
    return `SELECT hds.desc FROM hotel_daily_settlement hds INNER JOIN
        hotel_rent hr ON hds.hotelId=hr.hotelId AND ${date[0]}<hds.from AND ${date[1]}>hds.from`;
}
function rentalSQL(date){//租金数据
    return `SELECT hds.desc,hds.isSettled,hds.amount FROM hotel_daily_settlement hds INNER JOIN 
    hotel_rent hr ON hds.hotelId=hr.hotelId AND hds.from>${date[0]} AND hds.from<${date[1]}`;
}
function nightnumSQL2(date,hotellist){//总夜间晚数night*roomnum
    return `SELECT SUM(num) num FROM (SELECT night*roomnum num FROM (
        SELECT hds.hotelId,night,COUNT(*) roomnum FROM (
        SELECT hds.hotelId,COUNT(*) night FROM hotel_daily_settlement hds INNER JOIN
        hotel_rent hr ON hds.hotelId=hr.hotelId AND ${date[0]}<hds.from AND ${date[1]}>hds.from WHERE hds.hotelId IN (${hotellist}) GROUP BY hotelId
        ) hds INNER JOIN hotel_room hr ON hds.hotelId=hr.hotelId GROUP BY hotelId
        ) hds ) hds`;
}
function occupancySQL2(date,hotellist){//总入住数desc
    return `SELECT hds.hotelId,hds.desc,hds.amount FROM hotel_daily_settlement hds INNER JOIN
        hotel_rent hr ON hds.hotelId=hr.hotelId AND ${date[0]}<hds.from AND ${date[1]}>hds.from WHERE hds.hotelId IN (${hotellist})`;
}
module.exports={
    hotelNumChangeSQL,
    nightnumSQL,
    occupancySQL,
    nightnumSQL2,
    occupancySQL2,
    rentalSQL
}